﻿using System;
using System.Data;
using m_pricelist._DAL_;
using Excel = Microsoft.Office.Interop.Excel;

namespace m_pricelist._BAL_
{
    public class Products 
    {
        ProductsData DLobj = new ProductsData();
        
        public Products() 
        {
        }  
       
        Excel.Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook; 
        //Nær í DataSet frá getD_Appendix og skilar til ReadDAppendix í Proposal. Er notað til að fylla Combo- og list boxin á Appendix tab
        public DataSet getB_Appendix()
        {
            DataSet ds = new DataSet();
            ds = DLobj.getD_Appendix();
            return ds;
        }

        public DataSet getIndustry()
        {
            DataSet ds = new DataSet();
            ds = DLobj.getIndustry();
            return ds;
        }

        public DataSet getSubIndustry(string selectedIndustry)
        {
            DataSet ds = new DataSet();
            ds = DLobj.getSubIndustry(selectedIndustry);
            return ds;
        }

        public DataSet getProductUnits()
        {
            DataSet ds = new DataSet();
            ds = DLobj.getProductUnits();
            return ds;            
        }

        public DataSet getProductTypes(string strProUnit)
        {
            DataSet ds = new DataSet();
            ds = DLobj.getProductTypes(strProUnit);
            return ds;
        }

        public DataSet getProducts(string strProType)
        {
            DataSet ds = new DataSet();
            ds = DLobj.getProducts(strProType);
            return ds;
        }

        public DataSet getProduct(string strItemNumber)
        {
            DataSet ds = new DataSet();
            ds = DLobj.getProduct(strItemNumber);
            return ds;
        }

        public DataSet getSprItems(string strProType, string strProduct)
        {
            DataSet ds = new DataSet();
            ds = DLobj.getSprItemsData(strProType,strProduct);
            return ds;
        }

        public DataSet getSprItems(string strProType)
        {
            DataSet ds = new DataSet();
            ds = DLobj.getSprItemsData(strProType);
            return ds;
        }

        public string getGroupId(string strItemNumber)
        {
            string tempstr;
            tempstr = DLobj.getGroupId(strItemNumber);
            return tempstr;
        }

        public DataSet getOptAndConfGroups(string strGroupId)
        {
            DataSet ds = new DataSet();
            ds = DLobj.getOptAndConfGroups(strGroupId);
            return ds;
        }

        public DataSet getOptAndConfigItems(string strOptionGroup,string strGroupId)
        {
            DataSet ds = new DataSet();
            ds = DLobj.getOptAndConfItems(strOptionGroup, strGroupId);
            return ds;
        }

        public DataSet readFromExcelDoc()
        {
            DataSet dsExcel = new DataSet();
            DataTable excelTable = new DataTable();
            int excelRow = 10;
            int dataColumn = 3;
            
            Microsoft.Office.Interop.Excel._Worksheet worksheet = workbook.ActiveSheet;

            String str = Convert.ToString(workbook.Sheets["Output"].Cells(excelRow, dataColumn).Value);
            if (str == null)
            {
                return dsExcel;
            }
            else
            {
                while (Convert.ToString(workbook.Sheets["Output"].Cells(excelRow, dataColumn).Value) != "")
                {
                    str = Convert.ToString(workbook.Sheets["Output"].Cells(excelRow, dataColumn).Value);
                    excelRow++;
                }
                return dsExcel;
            }
        }

        //TODO Ekki notaður eins og er. Notum Interaction.GetSetting í staðinn - 28.6
        public string GetRegPath()
        {
            string RegPath = DLobj.getRegPath();
            return RegPath;
        }

        public string GetRegPath(String db)
        {
            string RegPath = DLobj.getRegPath(db);
            return RegPath;
        }


        /// <summary>
        /// Get the excel table data without the service view
        /// </summary>
        /// <param name="strItemNumber"></param>
        /// <param name="units"></param>
        /// <param name="sort1"></param>
        /// <returns></returns>
        public DataTable tableMaker(string strItemNumber,int units,string sort1,string sort2)
        {
            DataSet ds = new DataSet();

            if (DLobj.getExcelDataItems(strItemNumber).Tables[0].Rows.Count != 0)
            {
                DataColumn curColunm = new DataColumn();
                ds = DLobj.getExcelDataItems(strItemNumber);                
                ds.Tables[0].Columns.Add(curColunm);
                ds.Tables[0].Rows[0][5] = units;
                ds.Tables[0].Columns.Add("sort1", typeof(string));
                ds.Tables[0].Rows[0]["sort1"] = sort1;
                ds.Tables[0].Columns.Add("sort2", typeof(string));
                ds.Tables[0].Rows[0]["sort2"] = sort2;
                return ds.Tables[0];
            }
            else if (DLobj.getExcelDataOptions(strItemNumber).Tables[0].Rows.Count != 0)
            {
                DataColumn curColunm = new DataColumn();
                ds = DLobj.getExcelDataOptions(strItemNumber);
                ds.Tables[0].Rows[0][5] = units;
                ds.Tables[0].Columns.Add(curColunm);
                ds.Tables[0].Columns.Add("sort1", typeof(string));
                ds.Tables[0].Rows[0]["sort1"] = sort1;
                ds.Tables[0].Columns.Add("sort2", typeof(string));
                ds.Tables[0].Rows[0]["sort2"] = sort2;
                return ds.Tables[0];
            }
            else
                System.Windows.Forms.MessageBox.Show("ERROR ERROR");
                return null;
        }

        /// <summary>
        /// Get the service view data
        /// </summary>
        /// <param name="strItemNumber"></param>
        /// <param name="sort1"></param>
        /// <returns></returns>
        public DataTable tableMaker(string strItemNumber, string strProductTypeName)
        {
            DataSet ds = DLobj.getServiceValueData(strItemNumber);
            ds.Tables[0].TableName = strProductTypeName;
            return ds.Tables[0];
        }

        /// <summary>
        /// Get items from sprQuoteItems and sprQuoteOptions for sprQuote
        /// </summary>
        /// <param name="strItemNumber"></param>
        /// <returns></returns>
        public DataTable tableMaker(string strItemNumber,int units)
        {
            DataSet ds = new DataSet();

            if (DLobj.getSprQuoteItems(strItemNumber).Tables[0].Rows.Count != 0)
            {
                ds = DLobj.getSprQuoteItems(strItemNumber);
                ds.Tables[0].Rows[0][3] = units;
                return ds.Tables[0];
            }
            else if (DLobj.getSprQuoteOptions(strItemNumber).Tables[0].Rows.Count != 0)
            {
                ds = DLobj.getSprQuoteOptions(strItemNumber);
                ds.Tables[0].Rows[0][3] = units;
                return ds.Tables[0];
            }
            else
            {
                System.Windows.Forms.MessageBox.Show("ERROR ERROR");
                return null;
            }
        }

        public DataTable excelTableMaker()
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            dt.TableName = "TestTafla";
            
            DataColumn dc = new DataColumn();
            ds.Tables.Add(dt);            
            ds.Tables[0].Columns.Add(dc);

            int column = 3;
            int excelSheetRow = 10;
            int tempTableRow = 0;

            dt.Columns[0].ColumnName = "ITEM_NUMBER";
            dt.Columns.Add("UNITS");
            dt.Columns.Add("DISCOUNT");
            dt.Columns.Add("PROUDUCT");
            dt.Columns.Add("PRODUCTTYPE");

            while (workbook.Sheets["Output"].Cells(excelSheetRow, column).Value != null)
            {

                if (workbook.Sheets["Output"].Cells(excelSheetRow, column + 2).Value != null)
                {
                    DataRow dr = ds.Tables["TestTafla"].NewRow();
                    ds.Tables["TestTafla"].Rows.Add(dr);

                    ds.Tables[0].Rows[tempTableRow][0] = workbook.Sheets["Output"].Cells(excelSheetRow, 3).Value;

                    ds.Tables[0].Rows[tempTableRow][1] = workbook.Sheets["Output"].Cells(excelSheetRow, 6).Value;

                    ds.Tables[0].Rows[tempTableRow][2] = workbook.Sheets["Output"].Cells(excelSheetRow, 8).Value;

                    ds.Tables[0].Rows[tempTableRow][3] = workbook.Sheets["Output"].Cells(excelSheetRow, 33).Value;

                    ds.Tables[0].Rows[tempTableRow][4] = workbook.Sheets["Output"].Cells(excelSheetRow, 32).Value;

                    tempTableRow++;
                }
                excelSheetRow++;
            }
  
            return ds.Tables[0];
        }

        public void excelLineRemover()
        {              
            Excel.Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
            // creating new Excelsheet in workbook
            Microsoft.Office.Interop.Excel._Worksheet worksheet = workbook.ActiveSheet;
            int numberOfLines = excelRowCounter(workbook)+12;

            for (int i = 0; i < numberOfLines; i++)
            {   
                //TODO lineRemover():taka litina burt úr excel
                //worksheet.Rows.EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White); 
                for (int j = 1; j < numberOfLines; j++)
                {
                    worksheet.Cells[i + 10, j] = "";          
                }
            }

        }

        internal dynamic excelRowCounter(Excel._Workbook workbook)
        {
            int rowCounter = 0;
            int row = 10;
            string cellContent = "";

            //TODO laga, þetta er ljót
            while (cellContent != null)
            {
                cellContent = Convert.ToString(workbook.Sheets["Output"].Cells(row, 3).Value);
                if (cellContent == null)
                {
                    return rowCounter;
                }
                rowCounter++;
                row++;
            }
            return rowCounter;
        }

        //TODO dashToDot(string number) er þetta notað ??
        /// <summary>
        /// Replaces "," with "." in a string 
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        public string dashToDot(string number)
        {
            string tempNumber = "";
            tempNumber = number.Replace(",", ".");
            return tempNumber;
        }

        public string dashAndDotRemover(string strNumber)
        {
            if (strNumber.Contains("."))
            {
                int number = strNumber.IndexOf(".");
                strNumber = strNumber.Remove(number, 1);
            }
            return strNumber;
        }

        internal dynamic grandtotalRow(Excel._Workbook workbook, String column)
        {  
            string tempString2 = "=";
  
            int numberOfLines =  excelRowCounter(workbook);

            for (int excelRow = 0; excelRow < numberOfLines; excelRow++)
            {
                string tempString = Convert.ToString(workbook.Sheets["Output"].Cells(excelRow + 10, 3).Value);

                if (tempString.Contains("Total"))
                {
                    tempString2 = tempString2 + column + (excelRow + 10) + "+";
                }
            }

            if (tempString2.Contains("+"))
            {
                tempString2 = tempString2.TrimEnd('+');
                return tempString2;
            }

            return tempString2;
        }

        /// <summary>
        /// Will return true if there is somthing in the excel doc when you open the quoteform
        /// </summary>
        public bool isExcelEmpty()
        {
            String itemNumber = Convert.ToString(workbook.Sheets["Output"].Cells(10, 3).Value);
            if (itemNumber == null) { return false; }//There is nothing in the excel sheet
            else{ return true; }//There is somthing in the excel sheet
        }

        /// <summary>
        /// Gets the datatable for the parameter to update
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public DataSet GetParmUpdate(string parameter)
        {
            DataSet ds = new DataSet();
            ds = DLobj.getParmUpdate(parameter);
            return ds;
        }

        public void setDb(string newDb)
        {
            DLobj.setDb(newDb);      
        }

        public void updateDB_basic(string updateParameter,string tempstring)
        {
            DLobj.updateDB_basic(updateParameter,tempstring);
        }
    }
}
